﻿/*
* CRM_Customer.cs
*
* 功 能： N/A
* 类 名： CRM_Customer
*
* Ver    变更日期             负责人     变更内容
* ───────────────────────────────────
* V1.0  2016-02-29 11:53:22    黄润伟    
*
* Copyright © 2015 www.xhdcrm.com All rights reserved.
*┌──────────────────────────────────┐
*│　版权所有：小黄豆                      　　　　　　　　　　　　　　│
*└──────────────────────────────────┘
*/
using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using XHD.DBUtility;//Please add references
namespace XHD.DAL
{
    /// <summary>
    /// 数据访问类:CRM_Customer
    /// </summary>
    public partial class CRM_Customer
    {
        public CRM_Customer()
        { }
        #region  BasicMethod

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(string id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from CRM_Customer");
            strSql.Append(" where id=@id ");
            SqlParameter[] parameters = {
                    new SqlParameter("@id", SqlDbType.VarChar,50)           };
            parameters[0].Value = id;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        public bool SheetExists(string id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from Sale_order_sheet");
            strSql.Append(" where id=@id ");
            SqlParameter[] parameters = {
                    new SqlParameter("@id", SqlDbType.VarChar,50)           };
            parameters[0].Value = id;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(XHD.Model.CRM_Customer model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into CRM_Customer(");
            strSql.Append("id,Serialnumber,cus_name,cus_add,cus_tel,cus_fax,cus_website,cus_industry_id,Provinces_id,City_id,cus_type_id,cus_level_id,cus_source_id,DesCripe,Remarks,emp_id,isPrivate,lastfollow,xy,cus_extend,isDelete,Delete_time,create_id,create_time,cus_email,contactsname,ismark,state,xhs_website,xhs_nickname,xhs_guanzhu,xhs_fun,xhs_collect,xhs_notes,xhs_offer,xhs_datatime,xhs_tandian,xhs_tandiantime,xhs_code,xhs_category,xhs_isPartner,wx_username,xhs_alipay,xhs_label,xhs_cuskind)");
            strSql.Append(" values (");
            strSql.Append("@id,@Serialnumber,@cus_name,@cus_add,@cus_tel,@cus_fax,@cus_website,@cus_industry_id,@Provinces_id,@City_id,@cus_type_id,@cus_level_id,@cus_source_id,@DesCripe,@Remarks,@emp_id,@isPrivate,@lastfollow,@xy,@cus_extend,@isDelete,@Delete_time,@create_id,@create_time,@cus_email,@contactsname,@ismark,@state,@xhs_website,@xhs_nickname,@xhs_guanzhu,@xhs_fun,@xhs_collect,@xhs_notes,@xhs_offer,@xhs_datatime,@xhs_tandian,@xhs_tandiantime,@xhs_code,@xhs_category,@xhs_isPartner,@wx_username,@xhs_alipay,@xhs_label,@xhs_cuskind)");
            SqlParameter[] parameters = {
                    new SqlParameter( "@id", SqlDbType.VarChar,500),
                    new SqlParameter( "@Serialnumber", SqlDbType.VarChar,500),
                    new SqlParameter( "@cus_name", SqlDbType.VarChar,500),
                    new SqlParameter( "@cus_add", SqlDbType.VarChar,500),
                    new SqlParameter( "@cus_tel", SqlDbType.VarChar,500),
                    new SqlParameter( "@cus_fax", SqlDbType.VarChar,500),
                    new SqlParameter( "@cus_website", SqlDbType.VarChar,500),
                    new SqlParameter( "@cus_industry_id", SqlDbType.VarChar,500),
                    new SqlParameter( "@Provinces_id", SqlDbType.VarChar,500),
                    new SqlParameter( "@City_id", SqlDbType.VarChar,500),
                    new SqlParameter( "@cus_type_id", SqlDbType.VarChar,500),
                    new SqlParameter( "@cus_level_id", SqlDbType.VarChar,500),
                    new SqlParameter( "@cus_source_id", SqlDbType.VarChar,500),
                    new SqlParameter( "@DesCripe", SqlDbType.VarChar,500),
                    new SqlParameter( "@Remarks", SqlDbType.VarChar,500),
                    new SqlParameter( "@emp_id", SqlDbType.VarChar,500),
                    new SqlParameter( "@isPrivate", SqlDbType.Int,4),
                    new SqlParameter( "@lastfollow", SqlDbType.DateTime),
                    new SqlParameter( "@xy", SqlDbType.VarChar,500),
                    new SqlParameter( "@cus_extend", SqlDbType.VarChar,500),
                    new SqlParameter( "@isDelete", SqlDbType.Int,4),
                    new SqlParameter( "@Delete_time", SqlDbType.DateTime),
                    new SqlParameter( "@create_id", SqlDbType.VarChar,500),
                    new SqlParameter( "@create_time", SqlDbType.DateTime),
                    new SqlParameter( "@cus_email", SqlDbType.VarChar,500),
                    new SqlParameter( "@contactsname", SqlDbType.VarChar,500),
                    new SqlParameter( "@ismark", SqlDbType.Int,4),
                    new SqlParameter( "@state", SqlDbType.Int,4),
                    new SqlParameter( "@xhs_website", SqlDbType.VarChar,500),
                    new SqlParameter( "@xhs_nickname", SqlDbType.VarChar,500),
                    new SqlParameter( "@xhs_guanzhu", SqlDbType.Int,4),
                    new SqlParameter( "@xhs_fun", SqlDbType.Int,4),
                    new SqlParameter( "@xhs_collect", SqlDbType.Int,4),
                    new SqlParameter( "@xhs_notes", SqlDbType.Int,4),
                    new SqlParameter( "@xhs_offer", SqlDbType.Decimal,9),
                    new SqlParameter( "@xhs_datatime", SqlDbType.DateTime),
                    new SqlParameter( "@xhs_tandian", SqlDbType.Decimal,9),
                    new SqlParameter( "@xhs_tandiantime", SqlDbType.DateTime),
                    new SqlParameter( "@xhs_code", SqlDbType.VarChar,500),
                    new SqlParameter( "@xhs_category", SqlDbType.VarChar,500),
                    new SqlParameter( "@xhs_isPartner", SqlDbType.Int,4),
                    new SqlParameter( "@wx_username", SqlDbType.VarChar,500),
                    new SqlParameter( "@xhs_alipay", SqlDbType.VarChar,500),
                    new SqlParameter( "@xhs_label", SqlDbType.VarChar,500),
                    new SqlParameter( "@xhs_cuskind", SqlDbType.VarChar,500)
            };
            parameters[0].Value = model.id;
            parameters[1].Value = model.Serialnumber;
            parameters[2].Value = model.cus_name;
            parameters[3].Value = model.cus_add;
            parameters[4].Value = model.cus_tel;
            parameters[5].Value = model.cus_fax;
            parameters[6].Value = model.cus_website;
            parameters[7].Value = model.cus_industry_id;
            parameters[8].Value = model.Provinces_id;
            parameters[9].Value = model.City_id;
            parameters[10].Value = model.cus_type_id;
            parameters[11].Value = model.cus_level_id;
            parameters[12].Value = model.cus_source_id;
            parameters[13].Value = model.DesCripe;
            parameters[14].Value = model.Remarks;
            parameters[15].Value = model.emp_id;
            parameters[16].Value = model.isPrivate;
            parameters[17].Value = model.lastfollow;
            parameters[18].Value = model.xy;
            parameters[19].Value = model.cus_extend;
            parameters[20].Value = model.isDelete;
            parameters[21].Value = model.Delete_time;
            parameters[22].Value = model.create_id;
            parameters[23].Value = model.create_time;
            parameters[24].Value = model.cus_email;
            parameters[25].Value = model.contactsname;
            parameters[26].Value = model._isMark;
            parameters[27].Value = model.State;
            parameters[28].Value = model.xhs_website;
            parameters[29].Value = model.xhs_nickname;
            parameters[30].Value = model.xhs_guanzhu;
            parameters[31].Value = model.xhs_fun;
            parameters[32].Value = model.xhs_collect;
            parameters[33].Value = model.xhs_notes;
            parameters[34].Value = model.xhs_offer;
            parameters[35].Value = model.xhs_datatime;
            parameters[36].Value = model.xhs_tandian;
            parameters[37].Value = model.xhs_tandiantime;
            parameters[38].Value = model.xhs_code;
            parameters[39].Value = model.xhs_category;
            parameters[40].Value = model.xhs_isPartner;
            parameters[41].Value = model.wx_username;
            parameters[42].Value = model.xhs_alipay;
            parameters[43].Value = model.xhs_label;
            parameters[44].Value = model.xhs_cuskind;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            return rows > 0 ? true : false;
        }
        public bool ChangePR(string id, string pr)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Sale_order_sheet set ");
            strSql.Append("xhs_pr=@xhs_pr");
            strSql.Append(" where id=@id ");
            SqlParameter[] parameters = { new SqlParameter("@xhs_pr", SqlDbType.VarChar, 250), new SqlParameter("@id", SqlDbType.VarChar, 50) };
            parameters[0].Value = pr;
            parameters[1].Value = id;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            return rows > 0 ? true : false;
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(XHD.Model.CRM_Customer model)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("update CRM_Customer set ");
                strSql.Append("Serialnumber = @Serialnumber,");
                strSql.Append("cus_name = @cus_name,");
                strSql.Append("cus_add = @cus_add,");
                strSql.Append("cus_tel = @cus_tel,");
                strSql.Append("cus_fax = @cus_fax,");
                strSql.Append("cus_website = @cus_website,");
                strSql.Append("cus_industry_id = @cus_industry_id,");
                strSql.Append("Provinces_id = @Provinces_id,");
                strSql.Append("City_id = @City_id,");
                strSql.Append("cus_type_id = @cus_type_id,");
                strSql.Append("cus_level_id = @cus_level_id,");
                strSql.Append("cus_source_id = @cus_source_id,");
                strSql.Append("DesCripe = @DesCripe,");
                strSql.Append("Remarks = @Remarks,");
                strSql.Append("emp_id = @emp_id,");
                strSql.Append("isPrivate = @isPrivate,");
                strSql.Append("lastfollow = @lastfollow,");
                strSql.Append("xy = @xy,");
                strSql.Append("cus_extend = @cus_extend,");
                strSql.Append("isDelete = @isDelete,");
                strSql.Append("Delete_time = @Delete_time,");
                strSql.Append("cus_email = @cus_email,");
                strSql.Append("contactsname = @contactsname,");
                strSql.Append("ismark = @ismark,");
                strSql.Append("state = @state,");
                strSql.Append("xhs_website = @xhs_website,");
                strSql.Append("xhs_nickname = @xhs_nickname,");
                strSql.Append("xhs_guanzhu = @xhs_guanzhu,");
                strSql.Append("xhs_fun = @xhs_fun,");
                strSql.Append("xhs_collect = @xhs_collect,");
                strSql.Append("xhs_notes = @xhs_notes,");
                strSql.Append("xhs_offer = @xhs_offer,");
                strSql.Append("xhs_datatime = @xhs_datatime,");
                strSql.Append("xhs_tandian = @xhs_tandian,");
                strSql.Append("xhs_tandiantime = @xhs_tandiantime,");
                strSql.Append("xhs_code = @xhs_code,");
                strSql.Append("xhs_category = @xhs_category,");
                strSql.Append("xhs_isPartner = @xhs_isPartner,");
                strSql.Append("wx_username = @wx_username,");
                strSql.Append("xhs_alipay = @xhs_alipay,");
                strSql.Append("xhs_label = @xhs_label,");
                strSql.Append("xhs_cuskind = @xhs_cuskind");

                strSql.Append(" where id=@id ");

                SqlParameter[] parameters = {
                    new SqlParameter( "@Serialnumber", SqlDbType.VarChar,500),
                    new SqlParameter( "@cus_name", SqlDbType.VarChar,500),
                    new SqlParameter( "@cus_add", SqlDbType.VarChar,500),
                    new SqlParameter( "@cus_tel", SqlDbType.VarChar,500),
                    new SqlParameter( "@cus_fax", SqlDbType.VarChar,500),
                    new SqlParameter( "@cus_website", SqlDbType.VarChar,500),
                    new SqlParameter( "@cus_industry_id", SqlDbType.VarChar,500),
                    new SqlParameter( "@Provinces_id", SqlDbType.VarChar,500),
                    new SqlParameter( "@City_id", SqlDbType.VarChar,500),
                    new SqlParameter( "@cus_type_id", SqlDbType.VarChar,500),
                    new SqlParameter( "@cus_level_id", SqlDbType.VarChar,500),
                    new SqlParameter( "@cus_source_id", SqlDbType.VarChar,500),
                    new SqlParameter( "@DesCripe", SqlDbType.VarChar,500),
                    new SqlParameter( "@Remarks", SqlDbType.VarChar,500),
                    new SqlParameter( "@emp_id", SqlDbType.VarChar,500),
                    new SqlParameter( "@isPrivate", SqlDbType.Int,4),
                    new SqlParameter( "@lastfollow", SqlDbType.DateTime),
                    new SqlParameter( "@xy", SqlDbType.VarChar,500),
                    new SqlParameter( "@cus_extend", SqlDbType.VarChar,500),
                    new SqlParameter( "@isDelete", SqlDbType.Int,4),
                    new SqlParameter( "@Delete_time", SqlDbType.DateTime),
                    new SqlParameter( "@cus_email", SqlDbType.VarChar,500),
                    new SqlParameter( "@contactsname", SqlDbType.VarChar,500),
                    new SqlParameter( "@ismark", SqlDbType.Int,4),
                    new SqlParameter( "@state", SqlDbType.Int,4),
                    new SqlParameter( "@xhs_website", SqlDbType.VarChar,500),
                    new SqlParameter( "@xhs_nickname", SqlDbType.VarChar,500),
                    new SqlParameter( "@xhs_guanzhu", SqlDbType.Int,4),
                    new SqlParameter( "@xhs_fun", SqlDbType.Int,4),
                    new SqlParameter( "@xhs_collect", SqlDbType.Int,4),
                    new SqlParameter( "@xhs_notes", SqlDbType.Int,4),
                    new SqlParameter( "@xhs_offer", SqlDbType.Decimal,9),
                    new SqlParameter( "@xhs_datatime", SqlDbType.DateTime),
                    new SqlParameter( "@xhs_tandian", SqlDbType.Decimal,9),
                    new SqlParameter( "@xhs_tandiantime", SqlDbType.DateTime),
                    new SqlParameter( "@xhs_code", SqlDbType.VarChar,500),
                    new SqlParameter( "@xhs_category", SqlDbType.VarChar,500),
                    new SqlParameter( "@xhs_isPartner", SqlDbType.Int,4),
                    new SqlParameter( "@wx_username", SqlDbType.VarChar,500),
                    new SqlParameter( "@xhs_alipay", SqlDbType.VarChar,500),
                    new SqlParameter( "@xhs_label", SqlDbType.VarChar,500),
                    new SqlParameter( "@xhs_cuskind", SqlDbType.VarChar,500),
                    new SqlParameter( "@id", SqlDbType.VarChar,500)
            };
                parameters[0].Value = model.Serialnumber;
                parameters[1].Value = model.cus_name;
                parameters[2].Value = model.cus_add;
                parameters[3].Value = model.cus_tel;
                parameters[4].Value = model.cus_fax;
                parameters[5].Value = model.cus_website;
                parameters[6].Value = model.cus_industry_id;
                parameters[7].Value = model.Provinces_id;
                parameters[8].Value = model.City_id;
                parameters[9].Value = model.cus_type_id;
                parameters[10].Value = model.cus_level_id;
                parameters[11].Value = model.cus_source_id;
                parameters[12].Value = model.DesCripe;
                parameters[13].Value = model.Remarks;
                parameters[14].Value = model.emp_id;
                parameters[15].Value = model.isPrivate;
                parameters[16].Value = model.lastfollow;
                parameters[17].Value = model.xy;
                parameters[18].Value = model.cus_extend;
                parameters[19].Value = model.isDelete;
                parameters[20].Value = model.Delete_time;
                parameters[21].Value = model.cus_email;
                parameters[22].Value = model.contactsname;
                parameters[23].Value = model._isMark;
                parameters[24].Value = model.State;
                parameters[25].Value = model.xhs_website;
                parameters[26].Value = model.xhs_nickname;
                parameters[27].Value = model.xhs_guanzhu;
                parameters[28].Value = model.xhs_fun;
                parameters[29].Value = model.xhs_collect;
                parameters[30].Value = model.xhs_notes;
                parameters[31].Value = model.xhs_offer;
                parameters[32].Value = model.xhs_datatime;
                parameters[33].Value = model.xhs_tandian;
                parameters[34].Value = model.xhs_tandiantime;
                parameters[35].Value = model.xhs_code;
                parameters[36].Value = model.xhs_category;
                parameters[37].Value = model.xhs_isPartner;
                parameters[38].Value = model.wx_username;
                parameters[39].Value = model.xhs_alipay;
                parameters[40].Value = model.xhs_label;
                parameters[41].Value = model.xhs_cuskind;

                parameters[42].Value = model.id;

                int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
                return rows > 0 ? true : false;
            }
            catch(Exception ex)
            {
                return false;
            }
        }

        public DataSet GetCustomerGrowList(string strWhere)
        {
            string sql = "SELECT CRM_Customer_grow.id,CRM_Customer_grow.xhs_code,CRM_Customer_grow.xhs_notes,CRM_Customer_grow.xhs_fun,CRM_Customer_grow.xhs_hudong,CRM_Customer_grow.xhs_guanzhu,CRM_Customer_grow.xhs_gathertime ";
            sql += "FROM CRM_Customer_grow INNER JOIN CRM_Customer ON CRM_Customer.xhs_code=CRM_Customer_grow.xhs_code ";
            if (!string.IsNullOrEmpty(strWhere)) sql += "WHERE " + strWhere;
            sql += " ORDER BY xhs_gathertime DESC";
            return DbHelperSQL.Query(sql);
        }
        public DataSet GetCustomerGrowList(int PageSize, int PageIndex, string strWhere, out string Total)
        {
            Total = "0";
            string sql = "SELECT CRM_Customer_grow.id,CRM_Customer_grow.xhs_code,CRM_Customer_grow.xhs_notes,CRM_Customer_grow.xhs_fun,CRM_Customer_grow.xhs_hudong,CRM_Customer_grow.xhs_guanzhu,CRM_Customer_grow.xhs_gathertime ,ROW_NUMBER() OVER(Order by xhs_gathertime  desc) AS n ";
            sql += "FROM CRM_Customer_grow INNER JOIN CRM_Customer ON CRM_Customer.xhs_code=CRM_Customer_grow.xhs_code ";
            if (!string.IsNullOrEmpty(strWhere)) sql += "WHERE " + strWhere;
            string total_sql = "SELECT COUNT(ID) FROM (" + sql + ") a";
            Total = DbHelperSQL.Query(total_sql).Tables[0].Rows[0][0].ToString();
            string s = "SELECT * FROM (" + sql + ") A  WHERE n BETWEEN " + (PageSize * (PageIndex - 1) + 1) + " AND " + PageSize * PageIndex;
            return DbHelperSQL.Query(sql);
        }
        public string GetXHSCode(string xhs_article_code)
        {
            if (string.IsNullOrEmpty(xhs_article_code)) return "";
            string sql = string.Format("SELECT CRM_Customer.xhs_code FROM CRM_Customer INNER JOIN Sale_order_sheet ON Sale_order_sheet.xhs_code=CRM_Customer.xhs_code WHERE Sale_order_sheet.xhs_article_code='{0}'", xhs_article_code);
            return (string)DbHelperSQL.ReadValue(sql);
        }
        public string GetCustomerID(string xhs_code)
        {
            if (string.IsNullOrEmpty(xhs_code)) return "";
            string sql = string.Format("SELECT CRM_Customer.id FROM CRM_Customer WHERE CRM_Customer.xhs_code='{0}'", xhs_code);
            return (string)DbHelperSQL.ReadValue(sql);
        }
        public DataTable GetCustomerID()
        {
            string sql = "SELECT CRM_Customer.id,CRM_Customer.xhs_code FROM CRM_Customer";
            return DbHelperSQL.GetDataTable(sql);
        }
        public DataTable GetXHSCode()
        {
            string sql = "SELECT CRM_Customer.xhs_code,Sale_order_sheet.xhs_article_code FROM CRM_Customer INNER JOIN Sale_order_sheet ON Sale_order_sheet.xhs_code=CRM_Customer.xhs_code ";
            return DbHelperSQL.GetDataTable(sql);
        }
        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(string id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from CRM_Customer ");
            strSql.Append(" where id=@id ");
            SqlParameter[] parameters = {
                    new SqlParameter("@id", SqlDbType.VarChar,50)           };
            parameters[0].Value = id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            return rows > 0 ? true : false;
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string idlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from CRM_Customer ");
            strSql.Append(" where id in (" + idlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            return rows > 0 ? true : false;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,Serialnumber,cus_name,cus_add,cus_tel,cus_fax,cus_email,cus_website,cus_industry_id,Provinces_id,City_id,contactsname,cus_type_id,cus_level_id,cus_source_id,DesCripe,Remarks,emp_id,isPrivate,lastfollow,xy,isDelete,Delete_time,create_id,create_time,cus_extend,xhs_website,xhs_nickname,");
            strSql.Append("xhs_guanzhu=ISNULL((select top 1 xhs_guanzhu from CRM_Customer_grow where xhs_code=CRM_Customer.xhs_code order by xhs_gathertime),xhs_guanzhu),");
            strSql.Append("xhs_fun=ISNULL((select top 1 xhs_fun from CRM_Customer_grow where xhs_code=CRM_Customer.xhs_code order by xhs_gathertime),xhs_fun),");
            strSql.Append("xhs_collect=ISNULL((select top 1 xhs_hudong from CRM_Customer_grow where xhs_code=CRM_Customer.xhs_code order by xhs_gathertime),xhs_collect),");
            strSql.Append("xhs_notes=ISNULL((select top 1 xhs_notes from CRM_Customer_grow where xhs_code=CRM_Customer.xhs_code order by xhs_gathertime),xhs_notes),");
            strSql.Append("xhs_offer,CONVERT(NVARCHAR(20), xhs_datatime,120) AS xhs_datatime,xhs_tandian,CONVERT(NVARCHAR(20),xhs_tandiantime,120) AS xhs_tandiantime,xhs_code,xhs_category,xhs_isPartner,wx_username,xhs_alipay,xhs_label ");
            strSql.Append(",(select params_name from Sys_Param where id = CRM_Customer.cus_industry_id) as cus_industry");
            strSql.Append(",(select params_name from Sys_Param where id = CRM_Customer.xhs_cuskind) as xhs_cuskind");
            strSql.Append(",(select params_name from Sys_Param where id = CRM_Customer.cus_level_id) as cus_level");
            strSql.Append(",(select params_name from Sys_Param where id = CRM_Customer.cus_type_id) as cus_type");
            strSql.Append(",(select params_name from Sys_Param where id = CRM_Customer.cus_source_id) as cus_source");
            strSql.Append(",(select City from Sys_Param_City where id = CRM_Customer.City_id) as City");
            strSql.Append(",(select Provinces from Sys_Param_Provinces where id = CRM_Customer.Provinces_id) as Provinces");
            strSql.Append(",(select dep_name from hr_department where id = (select dep_id from hr_employee where id = CRM_Customer.emp_id)) as department");
            strSql.Append(",(select name from hr_employee where id = CRM_Customer.emp_id) as employee");
            strSql.Append(" FROM CRM_Customer ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        public DataSet GetModel(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,Serialnumber,cus_name,cus_add,cus_tel,cus_fax,cus_email,cus_website,cus_industry_id,Provinces_id,City_id,contactsname,cus_type_id,cus_level_id,cus_source_id,DesCripe,Remarks,emp_id,isPrivate,lastfollow,xy,isDelete,Delete_time,create_id,create_time,cus_extend,xhs_website,xhs_nickname,");
            strSql.Append("xhs_guanzhu=ISNULL((select top 1 xhs_guanzhu from CRM_Customer_grow where xhs_code=CRM_Customer.xhs_code order by xhs_gathertime),xhs_guanzhu),");
            strSql.Append("xhs_fun=ISNULL((select top 1 xhs_fun from CRM_Customer_grow where xhs_code=CRM_Customer.xhs_code order by xhs_gathertime),xhs_fun),");
            strSql.Append("xhs_collect=ISNULL((select top 1 xhs_hudong from CRM_Customer_grow where xhs_code=CRM_Customer.xhs_code order by xhs_gathertime),xhs_collect),");
            strSql.Append("xhs_notes=ISNULL((select top 1 xhs_notes from CRM_Customer_grow where xhs_code=CRM_Customer.xhs_code order by xhs_gathertime),xhs_notes),");
            strSql.Append("xhs_offer,CONVERT(NVARCHAR(20), xhs_datatime,120) AS xhs_datatime,xhs_tandian,CONVERT(NVARCHAR(20),xhs_tandiantime,120) AS xhs_tandiantime,xhs_code,xhs_category,xhs_isPartner,wx_username,xhs_alipay,xhs_label,xhs_cuskind ");
            strSql.Append(",(select params_name from Sys_Param where id = CRM_Customer.cus_industry_id) as cus_industry");
            strSql.Append(",(select params_name from Sys_Param where id = CRM_Customer.xhs_cuskind) as cuskind");
            strSql.Append(",(select params_name from Sys_Param where id = CRM_Customer.cus_level_id) as cus_level");
            strSql.Append(",(select params_name from Sys_Param where id = CRM_Customer.cus_type_id) as cus_type");
            strSql.Append(",(select params_name from Sys_Param where id = CRM_Customer.cus_source_id) as cus_source");
            strSql.Append(",(select City from Sys_Param_City where id = CRM_Customer.City_id) as City");
            strSql.Append(",(select Provinces from Sys_Param_Provinces where id = CRM_Customer.Provinces_id) as Provinces");
            strSql.Append(",(select dep_name from hr_department where id = (select dep_id from hr_employee where id = CRM_Customer.emp_id)) as department");
            strSql.Append(",(select name from hr_employee where id = CRM_Customer.emp_id) as employee");
            strSql.Append(" FROM CRM_Customer ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" id,Serialnumber,cus_name,cus_add,cus_tel,cus_fax,cus_email,cus_website,cus_industry_id,Provinces_id,City_id,contactsname,cus_type_id,cus_level_id,cus_source_id,DesCripe,Remarks,emp_id,isPrivate,lastfollow,xy,isDelete,Delete_time,create_id,create_time,cus_extend,xhs_website,xhs_nickname,");
            strSql.Append("xhs_guanzhu=ISNULL((select top 1 xhs_guanzhu from CRM_Customer_grow where xhs_code=CRM_Customer.xhs_code order by xhs_gathertime),xhs_guanzhu),");
            strSql.Append("xhs_fun=ISNULL((select top 1 xhs_fun from CRM_Customer_grow where xhs_code=CRM_Customer.xhs_code order by xhs_gathertime),xhs_fun),");
            strSql.Append("xhs_collect=ISNULL((select top 1 xhs_hudong from CRM_Customer_grow where xhs_code=CRM_Customer.xhs_code order by xhs_gathertime),xhs_collect),");
            strSql.Append("xhs_notes=ISNULL((select top 1 xhs_notes from CRM_Customer_grow where xhs_code=CRM_Customer.xhs_code order by xhs_gathertime),xhs_notes),");
            strSql.Append("xhs_offer,CONVERT(NVARCHAR(20),xhs_datatime,120) AS xhs_datatime,xhs_tandian,CONVERT(NVARCHAR(20),xhs_tandiantime,120) AS xhs_tandiantime,xhs_code,xhs_category,xhs_isPartner,wx_username, xhs_alipay,xhs_label ");
            strSql.Append(",(select params_name from Sys_Param where id = CRM_Customer.cus_industry_id) as cus_industry");
            strSql.Append(",(select params_name from Sys_Param where id = CRM_Customer.xhs_cuskind) as xhs_cuskind");
            strSql.Append(",(select params_name from Sys_Param where id = CRM_Customer.cus_level_id) as cus_level");
            strSql.Append(",(select params_name from Sys_Param where id = CRM_Customer.cus_type_id) as cus_type");
            strSql.Append(",(select params_name from Sys_Param where id = CRM_Customer.cus_source_id) as cus_source");
            strSql.Append(",(select City from Sys_Param_City where id = CRM_Customer.City_id) as City");
            strSql.Append(",(select Provinces from Sys_Param_Provinces where id = CRM_Customer.Provinces_id) as Provinces");
            strSql.Append(",(select dep_name from hr_department where id = (select dep_id from hr_employee where id = CRM_Customer.emp_id)) as department");
            strSql.Append(",(select name from hr_employee where id = CRM_Customer.emp_id) as employee");
            strSql.Append(" FROM CRM_Customer ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetList(int PageSize, int PageIndex, string strWhere, string filedOrder, out string Total,string intData = "")
        {
            StringBuilder strSql_inner = new StringBuilder();
            StringBuilder strSql_grid = new StringBuilder();
            StringBuilder strSql_total = new StringBuilder();

            //联合数据
            strSql_inner.Append("( SELECT id, Serialnumber, cus_name, cus_add,ismark,state, cus_tel, cus_email,cus_fax, cus_website, contactsname,cus_industry_id, Provinces_id, City_id, cus_type_id, cus_level_id, cus_source_id, DesCripe, Remarks, emp_id, isPrivate, lastfollow, xy, isDelete, Delete_time, create_id, create_time,cus_extend,xhs_website,xhs_nickname \n");
            strSql_inner.Append(",cus_industry,cus_level, cus_type,cus_source,City,Provinces,department,employee\n");
            //strSql_inner.Append($",ROW_NUMBER() OVER(Order by {filedOrder}) AS n FROM \n");
            strSql_inner.Append($",ROW_NUMBER() OVER(Order by {filedOrder}) AS n , \n");

            strSql_inner.Append("xhs_guanzhu=ISNULL((select top 1 xhs_guanzhu from CRM_Customer_grow where xhs_code=w2.xhs_code order by xhs_gathertime),xhs_guanzhu),\n");
            strSql_inner.Append("xhs_fun=ISNULL((select top 1 xhs_fun from CRM_Customer_grow where xhs_code=w2.xhs_code order by xhs_gathertime),xhs_fun),\n");
            strSql_inner.Append("xhs_collect=ISNULL((select top 1 xhs_hudong from CRM_Customer_grow where xhs_code=w2.xhs_code order by xhs_gathertime),xhs_collect),\n");
            strSql_inner.Append("xhs_notes=ISNULL((select top 1 xhs_notes from CRM_Customer_grow where xhs_code=w2.xhs_code order by xhs_gathertime),xhs_notes),\n");

            strSql_inner.Append("xhs_offer,ISNULL(CONVERT(nvarchar(10), xhs_datatime,120),'') as xhs_datatime,xhs_tandian,ISNULL(CONVERT(nvarchar(10), xhs_tandiantime,120),'') as xhs_tandiantime,ISNULL(xhs_code,'') as xhs_code, \n");
            strSql_inner.Append("ISNULL(xhs_category,'') as xhs_category,xhs_isPartner,ISNULL(wx_username,'') as wx_username,ISNULL(xhs_alipay,'') as xhs_alipay,xhs_label,cuskind as xhs_cuskind FROM \n");
            strSql_inner.Append("(\n");
            strSql_inner.Append("SELECT \n");
            strSql_inner.Append(" * ");
            strSql_inner.Append(",(select params_name from Sys_Param where id = w1.cus_industry_id) as cus_industry \n");
            strSql_inner.Append(",(select params_name from Sys_Param where id = w1.xhs_cuskind) as cuskind \n");
            strSql_inner.Append(",(select params_name from Sys_Param where id = w1.cus_level_id) as cus_level \n");
            strSql_inner.Append(",(select params_name from Sys_Param where id = w1.cus_type_id) as cus_type \n");
            strSql_inner.Append(",(select params_name from Sys_Param where id = w1.cus_source_id) as cus_source \n");
            strSql_inner.Append(",(select City from Sys_Param_City where id = w1.City_id) as City \n");
            strSql_inner.Append(",(select Provinces from Sys_Param_Provinces where id = w1.Provinces_id) as Provinces \n");
            strSql_inner.Append(",(select dep_name from hr_department where id = (select dep_id from hr_employee where id = w1.emp_id)) as department \n");
            strSql_inner.Append(",(select name from hr_employee where id = w1.emp_id) as employee \n");
            strSql_inner.Append("FROM ( SELECT * from CRM_Customer UNION SELECT * FROM CRM_Customer where isPrivate = 1) as w1 \n");
            strSql_inner.Append(") w2 \n");
            if (strWhere.Trim() != "")
            {
                strSql_inner.Append(" WHERE " + strWhere + "\n");
            }
            strSql_inner.Append(") W3 \n");
            if (!string.IsNullOrEmpty(intData)) strSql_inner.Append("WHERE " + intData + " \n");
            //Total数据
            strSql_total.Append(" SELECT COUNT(ID) FROM ");
            strSql_total.Append(strSql_inner.ToString());

            //grid数据
            strSql_grid.Append(" SELECT * FROM ");
            strSql_grid.Append(strSql_inner.ToString());
            strSql_grid.Append((!string.IsNullOrEmpty(intData) ? " AND " : " WHERE ") + " n BETWEEN " + (PageSize * (PageIndex - 1) + 1) + " AND " + PageSize * PageIndex);
           
            Total = DbHelperSQL.Query(strSql_total.ToString()).Tables[0].Rows[0][0].ToString();
            return DbHelperSQL.Query(strSql_grid.ToString());
        }

        #endregion  BasicMethod
        #region  ExtensionMethod

        /// <summary>
        ///     批量转客户
        /// </summary>
        public bool Update_batch(Model.CRM_Customer model, string strWhere)
        {
            var strSql = new StringBuilder();
            strSql.Append("update CRM_Customer set ");
            strSql.Append("emp_id=@emp_id");
            strSql.Append(" where emp_id=@create_id");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" and " + strWhere);
            }
            SqlParameter[] parameters =
            {
                new SqlParameter("@emp_id", SqlDbType.VarChar, 50),
                new SqlParameter("@create_id", SqlDbType.VarChar, 50)
            };

            parameters[0].Value = model.emp_id;
            parameters[1].Value = model.create_id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            return rows > 0 ? true : false;
        }
        /// <summary>
        ///     批量转客户
        /// </summary>
        public bool Update_batch(Model.CRM_Customer model, string strWhere,int topNum)
        {
            var strSql = new StringBuilder();
            strSql.Append("update CRM_Customer set ");
            strSql.Append("emp_id=@emp_id");
            strSql.Append(" where emp_id=@create_id");
            if (strWhere.Trim() != "")
            {
                string topsql = "(Select top " + topNum + " id  from CRM_Customer  where "+ strWhere + " order by create_time)";
                strSql.Append(" and  id in " + topsql);
            }
            SqlParameter[] parameters =
            {
                new SqlParameter("@emp_id", SqlDbType.VarChar, 50),
                new SqlParameter("@create_id", SqlDbType.VarChar, 50)
            };

            parameters[0].Value = model.emp_id;
            parameters[1].Value = model.create_id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            //update 表 set 字段 = 新值 where 字段 in (Select top 10 字段 from 表)；
            return rows > 0 ? true : false;
        }

        /// <summary>
        ///     预删除
        /// </summary>
        public bool AdvanceDelete(string id, int isDelete, string time)
        {
            var strSql = new StringBuilder();
            strSql.Append("update CRM_Customer set ");
            strSql.Append($"isDelete={isDelete}");
            strSql.Append($",Delete_time='{ time }'");
            strSql.Append($" where id='{id}'");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            return rows > 0 ? true : false;
        }

        /// <summary>
        ///     更新最后跟进
        /// </summary>
        public bool UpdateLastFollow(string id)
        {
            var strSql = new StringBuilder();
            strSql.Append("update CRM_Customer set ");
            strSql.Append("ismark=1,[lastfollow] = (select max(follow_time) as follow_time from dbo.CRM_follow where CRM_Customer.id = CRM_follow.customer_id)");
            strSql.Append($" where CRM_Customer.id='{id}'");

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            return rows > 0 ? true : false;
        }

        /// <summary>
        /// 放弃客户
        /// </summary>
        /// <param name="id">id</param>   
        /// <param name="state">0为正常 1为公共客户池</param>       
        /// <returns></returns>
        public bool AbanDon(string strWhere, int state)
        {
            var strSql = new StringBuilder();
            strSql.Append("update CRM_Customer set ");
            strSql.Append("[state] = " + state);          
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            return rows > 0 ? true : false;
        }

        /// <summary>
        /// 客户认领
        /// </summary>
        /// <param name="id">id</param>   
        /// <param name="state">0为正常 1为公共客户池</param>
        /// <param name="emp_id">认领人ID</param>
        /// <returns></returns>
        public bool Claimlist(string strWhere, int state,string emp_id)
        {
            var strSql = new StringBuilder();
            strSql.Append("update CRM_Customer set ");
            strSql.Append("[state] = " + state);
            strSql.Append(",[emp_id] = '" + emp_id+"'");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            return rows > 0 ? true : false;
        }
        /// <summary>
        /// 更新拜访标记
        /// </summary>
        /// <param name="id">id</param>
        /// <param name="ismark">ismark</param>
        /// <returns></returns>
        public bool UpdateBFmark(string strWhere, int ismark)
        {
            var strSql = new StringBuilder();
            strSql.Append("update CRM_Customer set ");
            strSql.Append("[ismark] = "+ ismark);    
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            return rows > 0 ? true : false;
        }

        public DataSet Reports_year(string items, int year, string where)
        {
            Random rnd = new Random();
            string temptable = "#temp_customer_" + rnd.Next(1, 100);

            var strSql = new StringBuilder();
            strSql.Append("if OBJECT_ID('Tempdb.." + temptable + "') is not null ");
            strSql.Append("    drop TABLE  " + temptable + " ");
            //strSql.Append("go");
            strSql.Append(" begin ");
            //strSql.Append("    --预统计表 "+temptable+"");
            strSql.Append("    select ");
            strSql.Append("        params_name,'m'+convert(varchar,month(create_time)) mm,count(id)tNum into " + temptable + " ");
            strSql.Append("   from (select w1.*,(select params_name from Sys_Param where id = w1." + items + "_id) as params_name from CRM_Customer w1 ");
            strSql.Append("    where datediff(YEAR,w1.[create_time],'" + year + "-1-1')=0 and isDelete=0 ) w2 ");
            if (where.Trim() != "")
            {
                strSql.Append(" where " + where);
            }
            strSql.Append("    group by w2.params_name,'m'+convert(varchar,month(w2.create_time)) ");

            //strSql.Append("    --生成SQL");
            strSql.Append("    declare @sql varchar(8000) ");
            strSql.Append("    set @sql='select params_name items ' ");
            strSql.Append("    select @sql = @sql + ',sum(case mm when ' + char(39) +mm+ char(39) + ' then tNum else 0 end) ['+ mm +']' ");
            strSql.Append("        from (select distinct mm from " + temptable + ")as data ");
            strSql.Append("    set @sql = @sql + ' from " + temptable + " group by params_name' ");

            strSql.Append("    exec(@sql) ");
            strSql.Append(" end ");

            strSql.Append("    drop TABLE  " + temptable + " ");

            return DbHelperSQL.Query(strSql.ToString());
        }


        /// <summary>
        ///     同比环比【客户新增】
        /// </summary>
        /// <param name="dt1"></param>
        /// <param name="dt2"></param>
        /// <returns></returns>
        public DataSet Compared(string year1, string month1, string year2, string month2)
        {
            var strSql = new StringBuilder();
            strSql.Append(" select count(id) as yy,");
            strSql.Append(" SUM(case when YEAR( create_time)=('" + year1 + "') and MONTH(create_time)=('" + month1 + "') then 1 else 0 end) as dt1, ");
            strSql.Append(" SUM(case when YEAR( create_time)=('" + year2 + "') and MONTH(create_time)=('" + month2 + "') then 1 else 0 end) as dt2 ");
            strSql.Append($" FROM CRM_Customer WHERE isDelete=0 ");

            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        ///     客户类型【同比环比】
        /// </summary>
        /// <param name="dt1"></param>
        /// <param name="dt2"></param>
        /// <returns></returns>
        public DataSet Compared_type(string year1, string month1, string year2, string month2)
        {
            var strSql = new StringBuilder();
            strSql.Append(" select CustomerType as yy,count(CustomerType) as xx,");
            strSql.Append(" SUM(case when YEAR( create_time)=('" + year1 + "') and MONTH(create_time)=('" + month1 + "') then 1 else 0 end) as dt1, ");
            strSql.Append(" SUM(case when YEAR( create_time)=('" + year2 + "') and MONTH(create_time)=('" + month2 + "') then 1 else 0 end) as dt2 ");
            strSql.Append($" FROM (select (select params_name from Sys_Param where id = CRM_Customer.[cus_type_id]) as CustomerType,* from CRM_Customer) as w1 ");
            strSql.Append($" WHERE isDelete=0");
            strSql.Append("group by CustomerType");

            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        ///     客户级别【同比环比】
        /// </summary>
        /// <param name="dt1"></param>
        /// <param name="dt2"></param>
        /// <returns></returns>
        public DataSet Compared_level(string year1, string month1, string year2, string month2 )
        {
            var strSql = new StringBuilder();
            strSql.Append(" select CustomerLevel as yy,count(CustomerLevel) as xx,");
            strSql.Append(" SUM(case when YEAR( create_time)=('" + year1 + "') and MONTH(create_time)=('" + month1 + "') then 1 else 0 end) as dt1, ");
            strSql.Append(" SUM(case when YEAR( create_time)=('" + year2 + "') and MONTH(create_time)=('" + month2 + "') then 1 else 0 end) as dt2 ");
            strSql.Append(" FROM (select (select params_name from Sys_Param where id = CRM_Customer.[cus_level_id]) as CustomerLevel ,* from CRM_Customer) as w1");
            strSql.Append($" WHERE isDelete=0 ");
            strSql.Append("group by CustomerLevel");

            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        ///     客户来源【同比环比】
        /// </summary>
        /// <param name="dt1"></param>
        /// <param name="dt2"></param>
        /// <returns></returns>
        public DataSet Compared_source(string year1, string month1, string year2, string month2)
        {
            var strSql = new StringBuilder();
            strSql.Append(" select CustomerSource as yy,count(CustomerSource) as xx,");
            strSql.Append(" SUM(case when YEAR( create_time)=('" + year1 + "') and MONTH(create_time)=('" + month1 + "') then 1 else 0 end) as dt1, ");
            strSql.Append(" SUM(case when YEAR( create_time)=('" + year2 + "') and MONTH(create_time)=('" + month2 + "') then 1 else 0 end) as dt2 ");
            strSql.Append(" FROM (select (select params_name from Sys_Param where id = CRM_Customer.[cus_Source_id]) as [CustomerSource],* from CRM_Customer) as w1 ");
            strSql.Append($" WHERE isDelete=0 ");
            strSql.Append("group by CustomerSource");

            return DbHelperSQL.Query(strSql.ToString());
        }

        public DataSet Compared_empcusadd(string year1, string month1, string year2, string month2, string idlist)
        {
            var strSql = new StringBuilder();
            strSql.Append(" select hr_employee.name as yy,");
            strSql.Append(" SUM(case when YEAR( CRM_Customer.create_time)=('" + year1 + "') and MONTH(CRM_Customer.create_time)=('" + month1 + "') then 1 else 0 end) as dt1, ");
            strSql.Append(" SUM(case when YEAR( CRM_Customer.create_time)=('" + year2 + "') and MONTH(CRM_Customer.create_time)=('" + month2 + "') then 1 else 0 end) as dt2 ");
            strSql.Append(" FROM hr_employee left outer join  CRM_Customer ");
            strSql.Append(" on hr_employee.id = CRM_Customer.Create_id ");
            strSql.Append(" where  CRM_Customer.isDelete=0 and hr_employee.id in " + idlist);
            strSql.Append(" group by hr_employee.name,hr_employee.id ");
            //strSql.Append(" order by hr_employee.id ");

            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 客户新增统计
        /// </summary>
        public DataSet report_empcus(int year, string idlist)
        {
            var strSql = new StringBuilder();
            strSql.Append(" select name,yy,isnull([1],0) as 'm1',isnull([2],0) as 'm2',isnull([3],0) as 'm3',isnull([4],0) as 'm4',isnull([5],0) as 'm5',isnull([6],0) as 'm6',");
            strSql.Append(" isnull([7],0) as 'm7',isnull([8],0) as 'm8',isnull([9],0) as 'm9',isnull([10],0) as 'm10',isnull([11],0) as 'm11',isnull([12],0) as 'm12' ");
            strSql.Append(" from");
            strSql.Append(" (SELECT   hr_employee.id, hr_employee.name, COUNT(derivedtbl_1.id) AS cn, YEAR(derivedtbl_1.create_time) AS yy, ");
            strSql.Append(" MONTH(derivedtbl_1.create_time) AS mm");
            strSql.Append(" FROM      hr_employee LEFT OUTER JOIN");
            strSql.Append("  (SELECT   id, Create_id, create_time");
            strSql.Append("  FROM      CRM_Customer");
            strSql.Append("  WHERE isdelete=0 and  (YEAR(create_time) = " + year + ")) AS derivedtbl_1 ON hr_employee.id = derivedtbl_1.Create_id");
            strSql.Append(" WHERE hr_employee.id in " + idlist);
            strSql.Append(" GROUP BY hr_employee.id, hr_employee.name, YEAR(derivedtbl_1.create_time), MONTH(derivedtbl_1.create_time)) as tt");
            strSql.Append(" pivot");
            strSql.Append(" (sum(cn) for mm in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]))");
            strSql.Append(" as pvt");

            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        ///  客户新增统计(月内)
        /// </summary>
        public DataSet report_month_empcus(string createTime, string idlist)
        {
            var strSql = new StringBuilder();
            strSql.Append(" select name,yy,isnull([1],0) as 'm1',isnull([2],0) as 'm2',isnull([3],0) as 'm3',isnull([4],0) as 'm4',isnull([5],0) as 'm5',isnull([6],0) as 'm6',");
            strSql.Append(" isnull([7],0) as 'm7',isnull([8],0) as 'm8',isnull([9],0) as 'm9',isnull([10],0) as 'm10',isnull([11],0) as 'm11',isnull([12],0) as 'm12' ");
            strSql.Append(" from");
            strSql.Append(" (SELECT   hr_employee.id, hr_employee.name, COUNT(derivedtbl_1.id) AS cn, YEAR(derivedtbl_1.create_time) AS yy, ");
            strSql.Append(" MONTH(derivedtbl_1.create_time) AS mm");
            strSql.Append(" FROM      hr_employee LEFT OUTER JOIN");
            strSql.Append("  (SELECT   id, Create_id, create_time");
            strSql.Append("  FROM      CRM_Customer");
            strSql.Append("  WHERE isdelete=0  and  " + createTime + ") AS derivedtbl_1 ON hr_employee.id = derivedtbl_1.Create_id");
            strSql.Append(" WHERE hr_employee.id in " + idlist);
            strSql.Append(" GROUP BY hr_employee.id, hr_employee.name, YEAR(derivedtbl_1.create_time), MONTH(derivedtbl_1.create_time)) as tt");
            strSql.Append(" pivot");
            strSql.Append(" (sum(cn) for mm in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]))");
            strSql.Append(" as pvt");

            return DbHelperSQL.Query(strSql.ToString());
        }


        /// <summary>
        ///     统计漏斗
        /// </summary>
        public DataSet Funnel(string strWhere, string year)
        {
            var strSql = new StringBuilder();
            strSql.Append(" select * from ");
            strSql.Append("( ");
            strSql.Append("	select  ");
            strSql.Append("		a.params_name as CustomerType, ");
            strSql.Append("		a.id as CustomerType_id, ");
            strSql.Append("		a.params_order , ");
            strSql.Append("		COUNT(b.id) as cc  ");
            strSql.Append("	from  ");
            strSql.Append("		Sys_Param as a left join ( ");
            strSql.Append("			select * from CRM_Customer  ");

            if (year.Trim() != "")
            {
                strSql.Append("			where datediff(year,create_time,'" + year + "-01-01')=0  ");
            }

            strSql.Append("			)as b  ");
            strSql.Append("		on a.id = b.cus_type_id  ");
            strSql.Append("	where a.params_type = 'cus_type' ");

            if (strWhere.Trim() != "")
            {
                strSql.Append(" and  " + strWhere);
            }

            strSql.Append("	group by  ");
            strSql.Append("		a.params_name, ");
            strSql.Append("		a.id, ");
            strSql.Append("		a.params_order ");
            strSql.Append(") as t1 ");
            strSql.Append("order by params_order ");

            return DbHelperSQL.Query(strSql.ToString());
        }

        public DataSet GetMapList(string strWhere)
        {
            var strSql = new StringBuilder();
            strSql.Append("SELECT ");
            strSql.Append("     [id],[cus_name],[cus_add],[cus_tel],[xy]     ");
            strSql.Append("FROM [dbo].[CRM_Customer] ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 更新手机数据
        /// </summary>
        public bool UpdateApp(XHD.Model.CRM_Customer model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update CRM_Customer set ");
            strSql.Append("cus_name=@cus_name,");
            strSql.Append("cus_add=@cus_add,");
            strSql.Append("cus_tel=@cus_tel,");
            strSql.Append("cus_fax=@cus_fax,");
            strSql.Append("cus_website=@cus_website,");
            strSql.Append("cus_industry_id=@cus_industry_id,");
            strSql.Append("Provinces_id=@Provinces_id,");
            strSql.Append("City_id=@City_id,");
            strSql.Append("cus_type_id=@cus_type_id,");
            strSql.Append("cus_level_id=@cus_level_id,");
            strSql.Append("cus_source_id=@cus_source_id,");
            strSql.Append("DesCripe=@DesCripe,");
            strSql.Append("Remarks=@Remarks,");
            strSql.Append("emp_id=@emp_id,");
            strSql.Append("isPrivate=@isPrivate");
            strSql.Append(" where id=@id ");
            SqlParameter[] parameters = {
                    new SqlParameter("@Serialnumber", SqlDbType.VarChar,250),
                    new SqlParameter("@cus_name", SqlDbType.VarChar,250),
                    new SqlParameter("@cus_add", SqlDbType.VarChar,250),
                    new SqlParameter("@cus_tel", SqlDbType.VarChar,250),
                    new SqlParameter("@cus_fax", SqlDbType.VarChar,250),
                    new SqlParameter("@cus_website", SqlDbType.VarChar,250),
                    new SqlParameter("@cus_industry_id", SqlDbType.VarChar,50),
                    new SqlParameter("@Provinces_id", SqlDbType.VarChar,50),
                    new SqlParameter("@City_id", SqlDbType.VarChar,50),
                    new SqlParameter("@cus_type_id", SqlDbType.VarChar,50),
                    new SqlParameter("@cus_level_id", SqlDbType.VarChar,50),
                    new SqlParameter("@cus_source_id", SqlDbType.VarChar,50),
                    new SqlParameter("@DesCripe", SqlDbType.VarChar,4000),
                    new SqlParameter("@Remarks", SqlDbType.VarChar,4000),
                    new SqlParameter("@emp_id", SqlDbType.VarChar,50),
                    new SqlParameter("@isPrivate", SqlDbType.Int,4),
                    new SqlParameter("@xy", SqlDbType.VarChar,50),
                    new SqlParameter("@id", SqlDbType.VarChar,50)
            };
            parameters[0].Value = model.Serialnumber;
            parameters[1].Value = model.cus_name;
            parameters[2].Value = model.cus_add;
            parameters[3].Value = model.cus_tel;
            parameters[4].Value = model.cus_fax;
            parameters[5].Value = model.cus_website;
            parameters[6].Value = model.cus_industry_id;
            parameters[7].Value = model.Provinces_id;
            parameters[8].Value = model.City_id;
            parameters[9].Value = model.cus_type_id;
            parameters[10].Value = model.cus_level_id;
            parameters[11].Value = model.cus_source_id;
            parameters[12].Value = model.DesCripe;
            parameters[13].Value = model.Remarks;
            parameters[14].Value = model.emp_id;
            parameters[15].Value = model.isPrivate;
            parameters[16].Value = model.xy;
            parameters[17].Value = model.id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            return rows > 0 ? true : false;
        }
        #endregion  ExtensionMethod
    }
}

